#!/usr/bin/env python
# -*- coding: utf-8 -*-

import sys
sys.path.append('./lib')
import sqlalchemy as sq
import sqlalchemy.orm as orm

#--------------------------------------------------
# DB Creation
db = sq.create_engine('sqlite://')
#db.echo = True
metadata = sq.BoundMetaData(db)
db.echo = True
#--------------------------------------------------
# Tables description
db_pref = 'wpdkp_'

t_members = sq.Table(db_pref+'members', metadata, 
sq.Column('memberID', sq.Integer, primary_key=True),
sq.Column('nick', sq.String(50), nullable=False, unique=True),
sq.Column('class', sq.Integer, nullable=False),
sq.Column('race', sq.Integer, nullable=False),
sq.Column('dkp', sq.Integer)
)

t_instances = sq.Table(db_pref+'instances', metadata, 
sq.Column('instanceID', sq.Integer, primary_key=True),
sq.Column('name', sq.String(50), nullable=False, unique=True)
)

t_bosses = sq.Table(db_pref+'bosses', metadata, 
sq.Column('bossID', sq.Integer, primary_key=True),
sq.Column('instanceID', sq.Integer, sq.ForeignKey(db_pref+'instances.instanceID')),
sq.Column('name', sq.Date, nullable=False, unique=True)

) 

t_raids = sq.Table(db_pref+'raids', metadata, 
sq.Column('raidID', sq.Integer, primary_key=True),
sq.Column('date', sq.Date, nullable=False, unique=True)
)

# Boss kills table
t_boss_kills= sq.Table(db_pref+'boss_kills', metadata, 
sq.Column('killID', sq.Integer, primary_key=True),
sq.Column('bossID', sq.Integer, sq.ForeignKey(db_pref+'bosses.bossID')),
sq.Column('raidID', sq.Integer, sq.ForeignKey(db_pref+'raids.raidID')),
sq.Column('memberID', sq.Integer, sq.ForeignKey(db_pref+'members.memberID')),
sq.Column('earned_dkp', sq.Integer)
)

# Boss drops table
t_loot_winners= sq.Table(db_pref+'loot_winners', metadata, 
sq.Column('killID', sq.Integer, sq.ForeignKey(db_pref+'bosses.bossID')),
sq.Column('memberID', sq.Integer, sq.ForeignKey(db_pref+'members.memberID')),
sq.Column('loot', sq.Integer),
)

# Create all tables if needed
metadata.create_all()

# Some functions
def addMember(m_nick, m_class, m_race):
	ins = t_members.insert()
	res = ins.execute({'nick':m_nick, 'class':m_class, 'race':m_race, 'dkp':10})

def getMember(nick=None):
	if(not nick):
		sel = t_members.select()
	else:
		sel = t_members.select(t_members.c.nick==nick)
	res = sel.execute()
	return res.fetchone() or False

def addInstance(name):
	ins = t_instances.insert()
	res = ins.execute({'name':name})
	return res.fetchone() or False

def getInstance(name=None):
	sel = t_instances.select(t_instances.c.name=='%s'%name)
	res = sel.execute()
	return res.fetchone() or False

def getBoss(name=None):
	sel = t_bosses.select(t_bosses.c.name=='%s'%name)
	res = sel.execute()
	return res.fetchone() or False

def getRaid(date=None):
	sel = t_raids.select(t_raids.c.date=='%s'%date)
	res = sel.execute()
	return res.fetchone() or False	

def addBoss(name, instance):
	ins = t_bosses.insert()
	res = ins.execute({'name':name, 'instanceID':instance})
	return res.fetchone() or False

def addRaid(date):
	ins = t_raids.insert()
	res = ins.execute({'date':date})
	return res.fetchone() or False

def addKillLoot(raid, boss, members, drops):
	# Find member IDs
	sel = sq.or_()
	for mem in members:	
		sel.append(t_members.c.nick==mem)
	# Find boss and raid IDs
	s = sq.select( [t_bosses.c.bossID,
					t_raids.c.raidID,
					t_members.c.memberID, t_members.c.nick
					], 
				sq.and_(t_bosses.c.name==boss, t_raids.c.date==raid))
	s.append_whereclause(sel)
	res = s.execute()
	recs = res.fetchall()
	res.close()
	up = t_boss_kills.insert()
	added = []
	ins = []
	for rec in recs:
		ins.append({'bossID':rec.bossID,
			'raidID':rec.raidID,
			'memberID':rec.memberID,
			'earned_dkp':1
			})
		added.append(rec.nick)
	up.execute(*ins)
	return added

# ----------------------------------

print "-"*50
addMember('Frou', 1, 1)
addMember('Dorg', 1, 1)
addMember('Summerin', 1, 1)
addBoss('Attumen the Huntsman', addInstance('Karazhan'))
addRaid('20070706')
print "-"*50
addKillLoot('20070706', 'Attumen the Huntsman', ['Frou', 'Dorg'], {12345:'Frou', 12354:'Dorg'})
print "ALL OK"

